<?php

require_once INC_CLASSPATH."/class.db.php";

class UserHelper
{

	private $db;

	private function initDB()
	{
		if(!isset($this->db)){
			$this->db = new DB();
			$this->db->connectFromParams();
		}
	}

	public function checkCredentials($login, $pwd)
	{
		$this->initDB();

		$pwd = md5($pwd);

		$SQL="SELECT * FROM users WHERE login='$login' AND password='$pwd'";
		$this->db->query($SQL);
		$count=$this->db->numRows();

		if($count>0){
			return true;
		}else{
			return false;
		}

	}

	public function getIdByLogin($login)
	{
		$this->initDB();

		$SQL="SELECT id FROM users WHERE login='$login'";
		$this->db->query($SQL);
		$count=$this->db->numRows();

		if($count>0){
			$row=$this->db->fetchArray();
			return $row["id"];
		}else{
			return -1;
		}
	}

	public function getRolesById($id){
		$this->initDB();

		$SQL="SELECT p.name
				FROM users AS u 
				     LEFT JOIN user_group_xref AS ug ON u.id=ug.user_id
				     LEFT JOIN groups AS g ON ug.group_id=g.id
				     LEFT JOIN group_permission_xref AS gp ON g.id=gp.group_id
				     LEFT JOIN permissions AS p ON gp.permission_id=p.id
				WHERE u.id=$id";

		$this->db->query($SQL);
		$count=$this->db->numRows();
		$perm = array();

		for($i=0;$i<$count;$i++)
		{
			$row=$this->db->fetchArray();
			$perm[] = $row["name"];
		}

		return $perm;

	}
	
	public function getAll(){
		$this->initDB();

		$SQL="SELECT u.*,g.id as groupId,g.name as groupName FROM users AS u 
				     LEFT JOIN user_group_xref AS ug ON u.id=ug.user_id
				     LEFT JOIN groups AS g ON ug.group_id=g.id";
		$this->db->query($SQL);
		$dataList=$this->db->fetch2Array();		
		$list = array();

		for($i=0;$i<count($dataList);$i++)
		{
			$perm = $this->getPermissionsListByGroupId($dataList[$i]["groupId"]);
			$dataList[$i]["permissions"] = $perm;
			$list[] = new UserVO($dataList[$i]);
		}
		
		return $list;
	}
	
	public function getAllGroups(){
		$this->initDB();

		$SQL="SELECT * FROM groups";
		$this->db->query($SQL);
		$dataList=$this->db->fetch2Array();
		$list = array();

		for($i=0;$i<count($dataList);$i++)
		{
			$dataList[$i]["permissions"] = $this->getPermissionsListByGroupId($dataList[$i]["id"]); 
			$list[] = new GroupVO($dataList[$i]);
		}
		
		return $list;
	}
	
	public function getAllPermissions(){
		$this->initDB();

		$SQL="SELECT * FROM permissions";
		$this->db->query($SQL);
		$dataList=$this->db->fetch2Array();
		$perm = array();

		for($i=0;$i<count($dataList);$i++)
		{
			$perm[] = new PermissionVO($dataList[$i]);
		}
		
		return $perm;
	}
	
	public function getPermissionsListByGroupId($groupId)
	{
		
		$SQL="SELECT p.id,p.name FROM group_permission_xref as gp 
			                LEFT JOIN permissions AS p ON gp.permission_id=p.id
				WHERE gp.group_id=$groupId ORDER BY p.id";
		$this->db->query($SQL);
		$count=$this->db->numRows();
		$perm = array();
		for($j=0;$j<$count;$j++)
		{
			$row=$this->db->fetchArray();
			$perm[] = new PermissionVO($row);
		}
		
		return $perm;
	}
	
	public function addUser(UserVO $user)
	{
		$this->initDB();
		
		$SQL="INSERT INTO users (firstname, lastname, email, login) 
				VALUES ('$user->firstname','$user->lastname','$user->email','$user->login')";
		
		$this->db->query($SQL);
		$userId = $this->db->insertId();
		$this->updateUserPasswordById($userId,md5($user->password));
		
		$SQL="INSERT INTO user_group_xref (user_id, group_id) 
				VALUES ($userId,$user->groupId)";
		$this->db->query($SQL);
		
		return array();
	}
	
	public function deleteUserById($userId)
	{
		$this->initDB();
		
		$SQL="DELETE FROM users  
				WHERE id=$userId";
		$this->db->query($SQL);
		
		$SQL="DELETE FROM user_group_xref  
				WHERE user_id=$userId";
		$this->db->query($SQL);
		
		return array();
	}
	
	public function updateUser(UserVO $user)
	{
		$this->initDB();
		
		$SQL="UPDATE users SET firstname='$user->firstname', lastname='$user->lastname', email='$user->email', login='$user->login'  
				WHERE id=$user->id";
		
		$this->db->query($SQL);
		if($user->password!=""){
			$this->updateUserPasswordById($user->id,md5($user->password));
		}
		
		$SQL="DELETE FROM user_group_xref  
				WHERE user_id=$user->id";
		$this->db->query($SQL);
		
		$SQL="INSERT INTO user_group_xref (user_id, group_id) 
				VALUES ($user->id,$user->groupId)";
		$this->db->query($SQL);
		
		return array();
	}
	
	private function updateUserPasswordById($userId,$pass)
	{
		$SQL="UPDATE users SET password='$pass' WHERE id=$userId";
		$this->db->query($SQL);
	}
	
	public function addGroup(GroupVO $group)
	{
		$this->initDB();
		
		$SQL="INSERT INTO groups (name) 
				VALUES ('$group->name')";
		
		$this->db->query($SQL);
		$groupId = $this->db->insertId();
		
		for($i=0;$i<count($group->permissions);$i++)
		{
			$perm = new PermissionVO($group->permissions[$i]);
			$SQL="INSERT INTO group_permission_xref (group_id, permission_id) 
					VALUES ($groupId,$perm->id)";
			$this->db->query($SQL);
		}
		
		return array();
	}
	
	public function deleteGroupById($groupId)
	{
		$this->initDB();
		
		$SQL="DELETE FROM groups  
				WHERE id=$groupId";
		$this->db->query($SQL);
		
		$SQL="DELETE FROM group_permission_xref  
				WHERE group_id=$groupId";
		$this->db->query($SQL);
		
		
		$SQL="SELECT user_id FROM user_group_xref 
				WHERE group_id=$groupId";
		$this->db->query($SQL);
		
		$dataList=$this->db->fetch2Array();		

		for($i=0;$i<count($dataList);$i++)
		{
			$SQL="DELETE FROM users  
				WHERE id=".$dataList[$i]["user_id"];
			$this->db->query($SQL);
		}
		
		$SQL="DELETE FROM user_group_xref  
				WHERE group_id=$groupId";
		$this->db->query($SQL);
		
		return array();
	}
	
	public function updateGroup(GroupVO $group)
	{
		$this->initDB();
		
		$SQL="UPDATE groups SET name='$group->name'  
				WHERE id=$group->id";
		
		$this->db->query($SQL);
		
		$SQL="DELETE FROM group_permission_xref  
				WHERE group_id=$group->id";
		$this->db->query($SQL);
		
		for($i=0;$i<count($group->permissions);$i++)
		{
			$perm = new PermissionVO($group->permissions[$i]);
			$SQL="INSERT INTO group_permission_xref (group_id, permission_id) 
					VALUES ($group->id,$perm->id)";
			$this->db->query($SQL);
		}
		
		return array();
	}

}
?>